﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace syc.finance
{
    public partial class 原料入出库管理 : System.Web.UI.Page
    {
        protected string Notice = "";
        string sql =
@"SELECT t1.ID
,t1.FID
,t1.单据号
,t1.原料ID
,t2.品名+'('+t2.钢级+')' as 品名
,t2.规格
,case when t1.入出=1 then
	case when t1.单位ID=1 then '一分厂'
	when t1.单位ID=2 then '二分厂'
	when t1.单位ID=3 then '三分厂'
	when t1.单位ID=4 then '四分厂'
	when t1.单位ID=5 then '五分厂'
	else
		'其他分厂'
	end
else
t3.名称
end as 供货或使用
,t1.日期
,入出
,cast(t1.重量 as float) as 重量
,t1.支数
,case when t1.计价方式=0 then cast(t1.重量*t1.单价 as numeric(18,3)) else cast(t1.支数*t1.单价 as numeric(18,3)) end as 金额
,cast(t1.单价 as float) as 单价
,cast(t1.结存重量 as float) as 结存重量
,t1.结存支数
,cast(t1.结存金额 as float) as 结存金额
,t1.保管员,
t1.凭证

FROM 原料入出库 t1
left outer join 原料 t2 on t2.ID=t1.原料ID
left outer join 原料供货单位 t3 on t3.ID=t1.单位ID";
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.Button_Query_Click(null, null);
            }
            else
            {
                this.SqlDataSource1.SelectCommand = this.SqlText.Text;
            }
        }

        protected void GridView1_DataBound(object sender, EventArgs e)
        {
            this.GridView1.SelectedIndex = -1;
            this.Button_Edit.Enabled = false;
            this.Button_Delete.Enabled = false;
            this.Button_Detail.Enabled = false;

            this.TextBox_Pager.Text = (this.GridView1.PageIndex + 1).ToString();
            this.Label_Pages.Text = " 共" + this.GridView1.PageCount.ToString() + "页 ";

            this.Button_Pager_First.CssClass = "pageturn-first-b";
            this.Button_Pager_First.Enabled = true;
            this.Button_Pager_Prev.CssClass = "pageturn-prev-b";
            this.Button_Pager_Prev.Enabled = true;
            this.Button_Pager_Next.CssClass = "pageturn-next-b";
            this.Button_Pager_Next.Enabled = true;
            this.Button_Pager_Last.CssClass = "pageturn-last-b";
            this.Button_Pager_Last.Enabled = true;
            if (this.GridView1.PageIndex == 0)
            {
                this.Button_Pager_Prev.CssClass = "pageturn-prev-g";
                this.Button_Pager_Prev.Enabled = false;
            }
            if (this.GridView1.PageIndex < 2)
            {
                this.Button_Pager_First.CssClass = "pageturn-first-g";
                this.Button_Pager_First.Enabled = false;
            }
            if (this.GridView1.PageIndex == this.GridView1.PageCount - 1 || this.GridView1.PageCount == 0)
            {
                this.Button_Pager_Next.CssClass = "pageturn-next-g";
                this.Button_Pager_Next.Enabled = false;

            }
            if (this.GridView1.PageIndex > this.GridView1.PageCount - 3)
            {
                this.Button_Pager_Last.CssClass = "pageturn-last-g";
                this.Button_Pager_Last.Enabled = false;
            }

        }
        protected void GridView1_PageIndexChanged(object sender, EventArgs e)
        {
            this.GridView1_DataBound(null, null);
        }
        protected void Pager_Click(object sender, EventArgs e)
        {
            if (sender.GetType().ToString() == "System.Web.UI.WebControls.Button")
            {
                switch (((Button)sender).CommandName)
                {
                    case "--":
                        this.GridView1.PageIndex = 0;
                        break;
                    case "-":
                        if (this.GridView1.PageIndex > 0) this.GridView1.PageIndex--;
                        break;
                    case "+":
                        if (this.GridView1.PageIndex < this.GridView1.PageCount - 1) this.GridView1.PageIndex++;
                        break;
                    case "++":
                        this.GridView1.PageIndex = this.GridView1.PageCount - 1;
                        break;
                }
            }
            else
            {

                int p;
                if (int.TryParse(TextBox_Pager.Text, out p))
                {
                    if (p >= 1 && p <= this.GridView1.PageCount)
                    {
                        this.GridView1.PageIndex = p - 1;
                    }
                }
                TextBox_Pager.Text = (this.GridView1.PageIndex + 1).ToString();
            }

        }
        protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
        {
            Label_Total.Text = "共查询到 " + e.AffectedRows + " 项记录";
        }
        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            int id;
            if ((int)this.GridView1.SelectedDataKey[2] == 0)
            {
                id = (int)this.GridView1.SelectedDataKey[0];
            }
            else
            {
                id = (int)this.GridView1.SelectedDataKey[2];
            }
            this.Button_Delete.CommandName = id.ToString();
            this.Button_Delete.Enabled = true;
            

            this.Button_Edit.Enabled = true;
            if ((byte)this.GridView1.SelectedDataKey[1] == 0)
            {
                this.Button_Edit.OnClientClick = "parent.swin2_load('/finance/原料入库单录入.aspx?id="
                    + id + "',770,440);return false;";
            }
            else
            {
                this.Button_Edit.OnClientClick = "parent.swin2_load('/Store/原料出库单录入.aspx?id="
                    + id + "',770,440);return false;";
            }

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;

            if ((byte)this.GridView1.SelectedDataKey[1] == 0)
            {
                Cmd.CommandText = "select id from 原料到货记录 where 入库单ID=" + id;
            }
            else
            {
                Cmd.CommandText = "select id from 原料发货记录 where 出库单ID=" + id;
            }


            SqlDataReader Reader = Cmd.ExecuteReader();
            string list = "";
            while (Reader.Read())
            {
                list += ","+((int)Reader[0]).ToString();
            }
            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
            if (list != "")
            {
                this.Button_Detail.OnClientClick = "parent.swin2_load('/Store/原料发货明细.aspx?list=" + list.Substring(1) + "',1100,800,'yes');return false;";
                this.Button_Detail.Enabled = true;
            }
            else
            {
                this.Button_Detail.Enabled = false;
            }
        }
        private string GetState()
        {
            string state = "";
            if (this.LeiBie.Text != "0" && this.LeiBie.Text != "")
            {
                state += " and t1.原料ID=" + this.LeiBie.Text;
            }

            DateTime d;
            if (DateTime.TryParse(this.Date1.Text, out d))
            {
                state += " and t1.日期>='" + d.ToShortDateString() + "'";
            }
            if (DateTime.TryParse(this.Date2.Text, out d))
            {
                state += " and t1.日期<='" + d.ToShortDateString() + "'";
            }
            if (this.GongHuo.Text != "0" && this.GongHuo.Text != "" && this.FenChang.Text != "0")
            {
                state += " and (t1.入出=0 and t1.单位ID=" + this.GongHuo.Text
                    + " or t1.入出=1 and t1.单位ID=" + this.FenChang.Text + ")";
            }
            else if (this.GongHuo.Text != "0" && this.GongHuo.Text != "" && this.FenChang.Text == "0")
            {
                state += " and (t1.入出=0 and t1.单位ID=" + this.GongHuo.Text
                    + " or t1.入出=1)";
            }
            else if (this.GongHuo.Text == "0" && this.FenChang.Text != "0")
            {
                state += " and (t1.入出=0 or t1.入出=1 and t1.单位ID=" + this.FenChang.Text + ")";
            }
            if (this.XianShi.Text != "")
            {
                state += " and t1.入出="+this.XianShi.Text;
            }
            if (state.StartsWith(" and"))
            {
                state = state.Substring(4);
            }
            if (state != "")
            {
                state = " where " + state;
            }
            return state;
        }
        protected void Button_Query_Click(object sender, EventArgs e)
        {

            string state = this.GetState();

            this.SqlText.Text = sql + state +" order by t1.id desc";
            this.SqlDataSource1.SelectCommand = "";
            this.SqlDataSource1.SelectCommand = this.SqlText.Text;
        }

        protected void Button_Reset_Click(object sender, EventArgs e)
        {

            this.LeiBie.Text = "0";
            this.GongHuo.Text = "0";
            this.FenChang.Text = "0";

            this.Date1.Text = "";
            this.Date2.Text = "";

        }

        protected void Button_Delete_Click(object sender, EventArgs e)
        {
            this.SqlDataSource1.DeleteParameters["ID"].DefaultValue = this.Button_Delete.CommandName;
            if (this.SqlDataSource1.Delete() > 0)
            {
                this.Notice = "·删除成功";
            }
            else
            {
                this.Notice = "·删除失败";
            }
        }

        protected void Button_Export_Click(object sender, EventArgs e)
        {
            if (this.LeiBie.Text == "0")
            {
                this.Notice = "·请选择一种材料";
                return;
            }
            string part1 =
@"<html xmlns:o='urn:schemas-microsoft-com:office:office'
xmlns:x='urn:schemas-microsoft-com:office:excel'
xmlns='http://www.w3.org/TR/REC-html40'>
<head>
<meta http-equiv=Content-Type content='text/html; charset=utf-8'>
<style>
<!--table
@page
{mso-header-data:;
mso-footer-data:;
margin:.79in .31in .79in .31in;
mso-header-margin:.51in;
mso-footer-margin:.51in;
mso-page-orientation:landscape;
mso-horizontal-page-align:center;
}
-->
</style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>原材料明细账</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>285</x:DefaultRowHeight>
     <x:Print>
      <x:ValidPrinterInfo/>
      <x:Scale>100</x:Scale>
      <x:PaperSizeIndex>9</x:PaperSizeIndex>
      <x:HorizontalResolution>200</x:HorizontalResolution>
      <x:VerticalResolution>200</x:VerticalResolution>
     </x:Print>
     <x:Selected/>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
       <x:ActiveRow>0</x:ActiveRow>
       <x:ActiveCol>0</x:ActiveCol>
      </x:Pane>
     </x:Panes>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body>";
            string part2 =
@"<style>
tr{height:24px;}
td{text-align:center;font-size:10pt;border:.5pt solid black;}
td.nb{border:none;}
</style>
<table border=0 cellpadding=0 style=border-collapse:collapse>
<tr>
<td class=nb height=40 colspan=12 style=font-size:18pt;><b><u>&nbsp;原 材 料 明 细 账&nbsp;</u></b></td>
</tr>";
            string part3 =
@"<tr>
<td class=nb colspan=3 style=text-align:left;>物资名称：_</td>
<td class=nb colspan=3 style=text-align:left;>钢级：_</td>
<td class=nb colspan=2 style=text-align:left;>单位：_</td>
<td class=nb colspan=4 style=text-align:left;>规格：_</td>

</tr>";
            string part4 =
@"<tr>
<td rowspan=2 width=70>日期</td>
<td rowspan=2 width=120>供货或使用单位</td>
<td rowspan=2 width=60>炉号</td>
<td rowspan=2 width=60>批号</td>
<td rowspan=2 width=50>单价</td>
<td colspan=2>收入</td>
<td colspan=2>支出</td>
<td colspan=2>结 存</td>
<td rowspan=2 width=70>稽核人员<br>盖章</td>
</tr>
<tr>
<td width=70>数量</td>
<td width=100>金额</td>
<td width=70>数量</td>
<td width=100>金额</td>
<td width=70>数量</td>
<td width=100>金额</td>
</tr>";
            string part5 =@"</table></body></html>";


            Response.Charset = "utf-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("原材料明细账") + ".xls\"");

            string[] gg = this.LeiBie.SelectedItem.Text.Split(new string[] { "(" }, 2, StringSplitOptions.None);
            part3 = part3.Replace("物资名称：_", "物资名称：" + gg[0]);
            part3 = part3.Replace("钢级：_", "钢级：" + gg[1].Replace(")", ""));
            part3 = part3.Replace("单位：_", "单位：吨");
            Response.Write(part1);
            Response.Write(part2);
            Response.Write(part3);
            Response.Write(part4);

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = sql + this.GetState() + " order by t1.ID";

            SqlDataReader Reader = Cmd.ExecuteReader();
            int n = 0;
            while (Reader.Read())
            {
                Response.Write("<tr><td>"+((DateTime)Reader["日期"]).ToShortDateString()+"</td>");
                Response.Write("<td>" + (string)Reader["供货或使用"] + "</td>");
                Response.Write("<td></td>");
                Response.Write("<td></td>");
                Response.Write("<td>" + ((double)Reader["单价"]).ToString() + "</td>");
                if ((byte)Reader["入出"] == 0)
                {
                    Response.Write("<td>" + ((double)Reader["重量"]).ToString() + "</td>");
                    Response.Write("<td>" + ((decimal)Reader["金额"]).ToString() + "</td>");
                    Response.Write("<td></td>");
                    Response.Write("<td></td>");
                }
                else
                {
                    Response.Write("<td></td>");
                    Response.Write("<td></td>");
                    Response.Write("<td>" + ((double)Reader["重量"]).ToString() + "</td>");
                    Response.Write("<td>" + ((decimal)Reader["金额"]).ToString() + "</td>");
                }
                Response.Write("<td>" + ((double)Reader["结存重量"]).ToString() + "</td>");
                Response.Write("<td>" + ((double)Reader["结存金额"]).ToString() + "</td>");
                Response.Write("<td></td>");
                n++;
            }
            Response.Write("<tr><td>合计：</td><td></td><td></td><td></td><td></td>");
            Response.Write("<td style=text-align:right x:num x:fmla='=SUM(F5:F"+(n+4)+")'></td>");
            Response.Write("<td style=text-align:right x:num x:fmla='=SUM(G5:G" + (n + 4) + ")'></td>");
            Response.Write("<td style=text-align:right x:num x:fmla='=SUM(H5:H" + (n + 4) + ")'></td>");
            Response.Write("<td style=text-align:right x:num x:fmla='=SUM(I5:I" + (n + 4) + ")'></td>");
            Response.Write("<td></td><td></td><td></td></tr>");

            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();

            Response.Write(part5);
            Response.End();
        }

    }
}
